﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace DAL
{
    using DAL.Entities;

    public class HoaDonTienDienDAL : BaseDAL, UTL.IBaseDAL
    {
        public int Count()
        {
            throw new NotImplementedException();
        }

        public DataTable Select()
        {
            string sql = @" SELECT *,b.ten_nv,d.ten_kh from hoadontientien a join nhanvien b on a.ma_nv=b.ma_nv join hopdongdien c on a.ma_hd=c.ma_hd join khachhang d on c.ma_kh=d.ma_kh join trangthai e on a.ma_trth=e.ma_trth join dienke f on a.ma_dk=f.ma_dk";
            return ExecuteQuery(sql);
        }

        public DataTable Select(object obj)
        {
            throw new NotImplementedException();
        }

        public object GetByKey(object key)
        {
            throw new NotImplementedException();
        }

        public bool Delete(string id)
        {
            string sql = @" DELETE FROM hoadontientien WHERE ma_hdtd = '{0}' ";
            sql = string.Format(sql,id);
            return ExecuteNonQuery(sql) > 0 ? true : false;
        }

        public bool Insert(object obj)
        {
            var o = (Hoa_Don_Tien_Dien)obj;
            string sql = @"set dateformat mdy;INSERT INTO hoadontientien(ma_hdtd,ngayghi,thuegtgt,tongkydien,tongtien,ma_nv,Ma_hd,ma_trth,ma_dk,ngayghisodien) VALUES('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}')";
            sql = string.Format(sql,o.ma_hdtd,o.ngayghi,o.thuegtgt,o.tongkydien,o.tongtien,o.ma_nv,o.ma_hd,o.matrth,o.madk,o.ngayghisodien);
            return ExecuteNonQuery(sql) > 0 ? true : false;
        }

        public bool Update_chisodienke(object obj,int a)
        {
            var o = (Hoa_Don_Tien_Dien)obj;
            string sql = @"set dateformat mdy;UPDATE chisodienke SET trangthai='{0}' WHERE ma_dk='{1}' and ngayghi='{2}'";
            sql = string.Format(sql,a,o.madk,o.ngayghisodien);
            return ExecuteNonQuery(sql) > 0 ? true : false;
        }

        public bool Update(object obj)
        {
            var o = (Hoa_Don_Tien_Dien)obj;
            string sql = @"set dateformat mdy;UPDATE hoadontientien SET ngayghi='{0}',thuegtgt={1},tongkydien={2},tongtien={3},ma_nv='{4}',ma_hd='{5}',ma_trth='{6}',ma_dk='{7}',ngayghisodien='{8}' WHERE ma_hdtd='{9}'";
            sql = string.Format(sql, o.ngayghi, o.thuegtgt, o.tongkydien, o.tongtien, o.ma_nv, o.ma_hd, o.matrth, o.madk, o.ngayghisodien, o.ma_hdtd);
            return ExecuteNonQuery(sql) > 0 ? true : false;
        }

        public DataTable Search(string name)
        {
            throw new NotImplementedException();
        }

        public DataTable Select_nhanvien()
        {
            string sql = @" SELECT ma_nv as id,ten_nv as ten FROM nhanvien ";
            return ExecuteQuery(sql);
        }

        public DataTable Select_khachhang()
        {
            string sql = @" SELECT a.ma_hd as id,b.ten_kh as ten FROM hopdongdien a join khachhang b on a.ma_kh=b.ma_kh";
            return ExecuteQuery(sql);
        }

        public DataTable Select_trangthai()
        {
            string sql = @"SELECT ma_trth as id,ten_trth as ten FROM trangthai";
            return ExecuteQuery(sql);
        }

        public DataTable Select_dienke()
        {
            string sql = @"SELECT ma_dk as id,diengiai as ten FROM dienke";
            return ExecuteQuery(sql);
        }
        public DataTable Select_ngayghidien()
        {
            string sql = @"SELECT ma_dk as id,ngayghi as ngayghisodien FROM chisodienke";
            return ExecuteQuery(sql);
        }
        public DataTable select_DK_theo_HD(string id)
        {
            string sql = @"SELECT ma_dk as id,diengiai as ten FROM dienke where ma_hd='{0}'";
            sql = string.Format(sql, id);
            return ExecuteQuery(sql);
        }
        public DataTable select_ngayghisodien_theo_DK(string id)
        {
            string sql = @"SELECT ngayghi as ngayghisodien FROM chisodienke where ma_dk='{0}' and trangthai=0";
            sql = string.Format(sql, id);
            return ExecuteQuery(sql);
        }

        public string select_gia(string id)
        {
            string sql = @"select gia from mucdichsudung a join hopdongdien b on a.ma_md=b.ma_md where ma_hd='{0}'";
            sql = string.Format(sql,id);
            DataTable dt = ExecuteQuery(sql);
            string kq=dt.Rows[0][0]+"";
            return kq.Trim();
        }
        public string select_dinhmuc(string id)
        {
            string sql = @"select dinhmuc from mucdichsudung a join hopdongdien b on a.ma_md=b.ma_md where ma_hd='{0}'";
            sql = string.Format(sql, id);
            DataTable dt = ExecuteQuery(sql);
            string kq = dt.Rows[0][0] + "";
            return kq.Trim();
        }
        public DataTable select_HDTDReport(string id)
        {
            string sql = @"select a.Ma_HDTD, e.Ten_KH as KhachHang,e.DiaChi as DiaChi,e.SDT as SDT,b.Ten_NV as NhanVien,a.Ma_DK,a.NgayGhi,(ThueGTGT*TongTien)as ThueGTGT,TongKyDien,TongTien,c.Ten_trth as TrangThai,f.ChiSoCu,f.ChiSoMoi
                        from HOADONTIENTIEN a join NHANVIEN b on a.Ma_NV=b.Ma_NV join TRANGTHAI c on a.Ma_TrTh=c.Ma_TrTh join HOPDONGDIEN d on a.Ma_HD=d.Ma_HD join KHACHHANG e on d.Ma_KH=e.Ma_KH join CHISODIENKE f on a.Ma_DK=f.Ma_DK and a.Ngayghisodien=f.NgayGhi
                        where a.Ma_HDTD='{0}' ";
            sql = string.Format(sql, id);
            return ExecuteQuery(sql);
        }
        public string selec_max()
        {
            string sql = @"select max(ma_hdtd) as max from hoadontientien";
            DataTable dt = ExecuteQuery(sql);
            return dt.Rows.Count > 0 ? "0" + dt.Rows[0]["max"] : "0";
        }
    }
}

